﻿CREATE PROCEDURE [dbo].[pokemons_get_listing]
   @page int = 0,
   @size int = 0,
   @column nvarchar(256),
   @direction int  = 0,
   @name nvarchar(256) = null,
   @element int = 0

AS
    SELECT PokemonId, Name, Element From Pokemons 
    Where 
        (@name is null OR Pokemons.Name LIKE '%' + @name + '%') AND
        (@element = 0 OR Pokemons.Element = @element)
    Order By
        Case When @column = 'Name' And @direction = 0 Then Name End ASC,
        Case When @column = 'Name' And @direction = 1 Then Name End DESC,
        Case When @column = 'Height' And @direction = 0 Then Height End ASC,
        Case When @column = 'Height' And @direction = 1 Then Height End DESC,
        Case When @column = 'Weight' And @direction = 0 Then Weight End ASC,
        Case When @column = 'Weight' And @direction = 1 Then Weight End DESC
    OFFSET ((@page -1)*@size) ROWS 
    FETCH NEXT @size ROWS ONLY;

Declare 
@TotalCount int
 SELECT @TotalCount= Count(DISTINCT  Pokemons.PokemonId)
	FROM  Pokemons
	where 
		(@name is NULL OR  Name like '%'+ @name + '%') AND
        (@element = 0 OR Pokemons.Element = @element)

RETURN @TotalCount;
